package net.huashitong.user.dao;

import com.sirdc.modules.core.dao.GenericQuery;
import com.sirdc.modules.core.dao.StringDao;
import jodd.util.StringUtil;
import net.huashitong.user.databean.OnLineDatabean;
import net.huashitong.user.entity.LoginLog;
import net.huashitong.user.filter.OnlineFilter;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.hibernate.type.StandardBasicTypes;
import org.springframework.stereotype.Repository;

import java.math.BigInteger;
import java.util.*;

@Repository
public class LoginLogDao extends StringDao<LoginLog> {
    public LoginLog getLoginLogByCreateDateAndUser(String createDate, LoginLog loginLog) {
        GenericQuery query = createQuery("obj");
        query.append(" and obj.phone =:phone").setParam("phone", loginLog.getPhone());
        query.append(" and obj.name =:name").setParam("name", loginLog.getName());
        query.append(" and obj.branch =:branch").setParam("branch", loginLog.getBranch());
        query.append(" and obj.role =:role").setParam("role", loginLog.getRole());
        query.append(" and obj.branchType =:branchType").setParam("branchType", loginLog.getBranchType());
        query.append(" and obj.createDate =:createDate").setParam("createDate", createDate);
        return query.firstResult();
    }

    public Map<String, Integer> queryDeptLoginCount() {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT branch, COUNT(*) from t_bnp_login_log WHERE branch_type='1' GROUP BY branch ORDER BY COUNT(*) DESC");
        SQLQuery query = getSession().createSQLQuery(sql.toString());
        List result = query.list();
        Iterator iterate = result.iterator();
        Map<String, Integer> map = new LinkedHashMap<String, Integer>();
        while (iterate.hasNext()) {
            Object[] value = (Object[]) iterate.next();
            BigInteger count = (BigInteger) value[1];
            map.put((String) value[0], count.intValue());
        }
        return map;
    }

    public List<OnLineDatabean> queryUserOnLineCount(OnlineFilter filter) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT sysid, name, num,branch,role, CASE WHEN @rowtotal = obj.NUM THEN @rownum WHEN @rowtotal \\:= obj.NUM THEN @rownum \\:=@rownum + 1 WHEN @rowtotal = 0 THEN @rownum \\:=@rownum + 1 END AS rownum FROM (SELECT U.sys_id sysid, U.phone phone,U.`name` name,U.branch branch," +
                "U.role role,count(L.phone) num FROM t_bnp_user U LEFT JOIN " +
                " (select sys_id, phone from t_bnp_login_log where 1 = 1 ");
        if(!StringUtil.isEmpty(filter.getStartDate())){
            sql.append(" AND str_to_date(create_date, '%Y-%m-%d') >= '" + filter.getStartDate() + "'");
        }
        if(!StringUtil.isEmpty(filter.getEndDate())){
            sql.append(" AND str_to_date(create_date, '%Y-%m-%d') <= '" + filter.getEndDate() +"'");
        }
        sql.append(" ) L ON U.phone = L.phone  where 1 = 1 ");
        if(!StringUtil.isEmpty(filter.getRole())){
            sql.append(" AND U.role = '" + filter.getRole() + "'");
        }
        if(!StringUtil.isEmpty(filter.getBranch())){
            sql.append(" AND U.branch like  '%" + filter.getBranch() + "%' ");
        }
        sql.append(" GROUP BY U.phone ORDER BY num DESC ) AS obj,( SELECT @rownum \\:= 0 ,@rowtotal \\:= NULL ) r ");
        SQLQuery query = getSession().createSQLQuery(sql.toString())
                .addScalar("sysid",StandardBasicTypes.STRING)
                .addScalar("name",StandardBasicTypes.STRING)
                .addScalar("num",StandardBasicTypes.INTEGER)
                .addScalar("branch",StandardBasicTypes.STRING)
                .addScalar("role",StandardBasicTypes.STRING)
                .addScalar("rownum",StandardBasicTypes.INTEGER); ;
        query.setResultTransformer(Transformers.aliasToBean(OnLineDatabean.class));
        List<OnLineDatabean> result = (List<OnLineDatabean>) query.list();
        return result;
    }

    public List<OnLineDatabean> queryUserOnlineList(OnlineFilter filter){
        StringBuffer sqlcount = new StringBuffer();
        sqlcount.append("SELECT count(DISTINCT(U.phone)) FROM t_bnp_user U LEFT JOIN t_bnp_login_log L ON U.phone = L.phone   where 1 = 1 ");
        if(!StringUtil.isEmpty(filter.getRole())){
            sqlcount.append(" AND U.role = '" + filter.getRole() +"' ");
        }
        if(!StringUtil.isEmpty(filter.getBranch())){
            sqlcount.append(" AND U.branch like  '%" + filter.getBranch() + "%' ");
        }
        SQLQuery querycount = getSession().createSQLQuery(sqlcount.toString());
        List resultcount = querycount.list();
        int total = Integer.parseInt(resultcount.get(0).toString());
        filter.getPaging().setPageCount(total/filter.getPaging().getPageSize() + 1);
        filter.getPaging().setRecords(total);
        StringBuffer sql = new StringBuffer();
        int fristnum = (filter.getPaging().getCurrentPage() - 1) * filter.getPaging().getPageSize();
        int endnum = fristnum + filter.getPaging().getPageSize();
        sql.append(" select * from ( SELECT sysid, name, num,branch,role, CASE WHEN @rowtotal = obj.NUM THEN @rownum WHEN @rowtotal \\:= obj.NUM THEN @rownum \\:=@rownum + 1 WHEN @rowtotal = 0 THEN @rownum \\:=@rownum + 1 END AS rownum FROM (SELECT U.sys_id sysid, U.phone phone,U.`name` name,U.branch branch," +
                "U.role role,count(L.phone) num FROM t_bnp_user U LEFT JOIN " +
                " (select sys_id, phone from t_bnp_login_log where 1 = 1 ");
        if(!StringUtil.isEmpty(filter.getStartDate())){
            sql.append(" AND str_to_date(create_date, '%Y-%m-%d') >= '" + filter.getStartDate() + "'");
        }
        if(!StringUtil.isEmpty(filter.getEndDate())){
            sql.append(" AND str_to_date(create_date, '%Y-%m-%d') <= '" + filter.getEndDate() +"'");
        }
        sql.append(" ) L ON U.phone = L.phone  where 1 = 1 ");
        if(!StringUtil.isEmpty(filter.getRole())){
            sql.append(" AND U.role = '" + filter.getRole() +"' ");
        }
        if(!StringUtil.isEmpty(filter.getBranch())){
            sql.append(" AND U.branch like  '%" + filter.getBranch() + "%' ");
        }
        if(!StringUtil.isEmpty(filter.getName())){
            sql.append(" AND U.name like  '%" + filter.getName() + "%' ");
        }
        if(!StringUtil.isEmpty(filter.getBranchType())){
            sql.append(" AND U.branch_type =  '" + filter.getBranchType() + "' ");
        }
        sql.append(" GROUP BY U.phone ORDER BY num DESC ) AS obj,( SELECT @rownum \\:= 0 ,@rowtotal \\:= NULL ) r ) c ");
        SQLQuery query = getSession().createSQLQuery(sql.toString())
                .addScalar("sysid",StandardBasicTypes.STRING)
                .addScalar("name",StandardBasicTypes.STRING)
                .addScalar("num",StandardBasicTypes.INTEGER)
                .addScalar("branch",StandardBasicTypes.STRING)
                .addScalar("role",StandardBasicTypes.STRING)
                .addScalar("rownum",StandardBasicTypes.INTEGER);
        query.setResultTransformer(Transformers.aliasToBean(OnLineDatabean.class));

        if (filter.getPaging() != null) {
            query.setFirstResult(filter.getPaging().getStartRow());
            query.setMaxResults(filter.getPaging().getPageSize());
        }

        List<OnLineDatabean> result = (List<OnLineDatabean>) query.list();
        return result;
    }


    public int queryUserOnlineListCount(){
        StringBuffer sqlcount = new StringBuffer();
        sqlcount.append("SELECT count(DISTINCT(U.phone)) FROM t_bnp_user U LEFT JOIN t_bnp_login_log L ON U.phone = L.phone   where 1 = 1 ");
//        if(!StringUtil.isEmpty(filter.getRole())){
//            sqlcount.append(" AND U.role = '" + filter.getRole() +"' ");
//        }
//        if(!StringUtil.isEmpty(filter.getBranch())){
//            sqlcount.append(" AND U.branch like  '%" + filter.getBranch() + "%' ");
//        }
        SQLQuery querycount = getSession().createSQLQuery(sqlcount.toString());
        List resultcount = querycount.list();
        int total = Integer.parseInt(resultcount.get(0).toString());
        return total;
    }


    public Map<String, Integer> queryUserLoginCount() {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT name, COUNT(*) from t_bnp_login_log WHERE branch_type='1' GROUP BY name ORDER BY COUNT(*) DESC");
        SQLQuery query = getSession().createSQLQuery(sql.toString());
        List result = query.list();
        Iterator iterate = result.iterator();
        Map<String, Integer> map = new LinkedHashMap<String, Integer>();
        while (iterate.hasNext()) {
            Object[] value = (Object[]) iterate.next();
            BigInteger count = (BigInteger) value[1];
            map.put((String) value[0], count.intValue());
        }
        return map;
    }

    public Map<String, Integer> queryLoginCount() {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT branch_type, COUNT(*) from t_bnp_login_log WHERE branch_type!='1' GROUP BY branch_type ORDER BY COUNT(*) DESC");
        SQLQuery query = getSession().createSQLQuery(sql.toString());
        List result = query.list();
        Iterator iterate = result.iterator();
        Map<String, Integer> map = new LinkedHashMap<String, Integer>();
        while (iterate.hasNext()) {
            Object[] value = (Object[]) iterate.next();
            BigInteger count = (BigInteger) value[1];
            String branchType = (String) value[0];
            switch (branchType) {
                case "2":
                    map.put("县重点工作攻坚指挥室", count.intValue());
                    break;
                case "0":
                    map.put("业主单位", count.intValue());
                    break;
                default:
                    break;
            }
        }
        return map;
    }
}
